<?php 
//
// Recruitment Agency Manager DB Upgrade Script
// This is a copy of the install script and is called in case that script hasn't 
// been updated by Joomla by an upgrade install of the component!
//

defined( '_JEXEC' ) or die( 'Direct Access to this location is not allowed.' ); 

// just a single function
function db_upgrade() {
	// check for the existance of new DB tables/fields we need in this version
	// and if they're not there, create them
	echo "<p>Performing any necessary upgrade actions on DB...</p>\n";

	//get the database configuration settings
	$conf =& JFactory::getConfig();
	$dbhost = $conf->getValue('config.host');
	$dbuser = $conf->getValue('config.user');
	$dbpassword = $conf->getValue('config.password');
	$dbname = $conf->getValue('config.db');
	$dbprefix = $conf->getValue('config.dbprefix');

	// connect to the database
	mysql_connect($dbhost,$dbuser,$dbpassword) or die("mysql_connect fail");
	mysql_select_db($dbname) or die("mysql_select_db fail");

	// first check the RAM_jobs table for the new bonusdue field
	$query = "SHOW COLUMNS FROM `".$dbprefix."RAM_jobs` LIKE 'bonusdue' ";
	$answer = mysql_query($query);
	// check for any error
	if (mysql_error()) { // check for problem
		echo "<p><b>Error reading column data:-</b> </p>\n" . mysql_error();
	} else {
		$num_rows=mysql_num_rows($answer);
		// Field found?
		if ($num_rows > 0 ) {
//			echo "<p>Field 'bonusdue' already exists.</p>\n";
		} else { // No, add it
//			echo "<p>Field 'bonusdue' not found!</p>\n";
			$query2 = "ALTER TABLE `".$dbprefix."RAM_jobs` ADD COLUMN `bonusdue` varchar(8) NOT NULL default '' ";
			$answer2 = mysql_query($query2);
			// check for any error
			if (mysql_error()) {
				echo "<p><b>Error adding field 'RAM_jobs.bonusdue':-</b> </p>\n" . mysql_error();
			} else {
				echo "<p>Field 'RAM_jobs.bonusdue' added OK.</p>\n";
			}
		} // eof check for record found
	}
	// now check the RAM_jobs table for the new bonuspaid field
	$query = "SHOW COLUMNS FROM `".$dbprefix."RAM_jobs` LIKE 'bonuspaid' ";
	$answer = mysql_query($query);
	// check for any error
	if (mysql_error()) { // check for problem
		echo "<p><b>Error reading column data:-</b> </p>\n" . mysql_error();
	} else {
		$num_rows=mysql_num_rows($answer);
		// Field found?
		if ($num_rows > 0 ) {
//			echo "<p>Field 'bonuspaid' already exists.</p>\n";
		} else { // No, add it
//			echo "<p>Field 'bonuspaid' not found!</p>\n";
			$query2 = "ALTER TABLE `".$dbprefix."RAM_jobs` ADD COLUMN `bonuspaid` varchar(8) NOT NULL default '' ";
			$answer2 = mysql_query($query2);
			// check for any error
			if (mysql_error()) {
				echo "<p><b>Error adding field 'RAM_jobs.bonuspaid':-</b> </p>\n" . mysql_error();
			} else {
				echo "<p>Field 'RAM_jobs.bonuspaid' added OK.</p>\n";
			}
		} // eof check for record found
	}
	// now check the RAM_jobs table for the new approved field
	$query = "SHOW COLUMNS FROM `".$dbprefix."RAM_jobs` LIKE 'approved' ";
	$answer = mysql_query($query);
	// check for any error
	if (mysql_error()) { // check for problem
		echo "<p><b>Error reading column data:-</b> </p>\n" . mysql_error();
	} else {
		$num_rows=mysql_num_rows($answer);
		// Field found?
		if ($num_rows > 0 ) {
//			echo "<p>Field 'approved' already exists.</p>\n";
		} else { // No, add it
//			echo "<p>Field 'approved' not found!</p>\n";
			$query2 = "ALTER TABLE `".$dbprefix."RAM_jobs` ADD COLUMN `approved` char(1) NOT NULL default 'n' ";
			$answer2 = mysql_query($query2);
			// check for any error
			if (mysql_error()) {
				echo "<p><b>Error adding field 'RAM_jobs.approved':-</b> </p>\n" . mysql_error();
			} else {
				echo "<p>Field 'RAM_jobs.approved' added OK.</p>\n";
			}
		} // eof check for record found
	}
	// now check the RAM_candidates table for the new keywords field
	$query = "SHOW COLUMNS FROM `".$dbprefix."RAM_candidates` LIKE 'keywords' ";
	$answer = mysql_query($query);
	// check for any error
	if (mysql_error()) { // check for problem
		echo "<p><b>Error reading column data:-</b> </p>\n" . mysql_error();
	} else {
		$num_rows=mysql_num_rows($answer);
		// Field found?
		if ($num_rows > 0 ) {
//			echo "<p>Field 'keywords' already exists.</p>\n";
		} else { // No, add it
//			echo "<p>Field 'keywords' not found!</p>\n";
			$query2 = "ALTER TABLE `".$dbprefix."RAM_candidates` ADD COLUMN `keywords` varchar(255) default NULL default '' ";
			$answer2 = mysql_query($query2);
			// check for any error
			if (mysql_error()) {
				echo "<p><b>Error adding field 'RAM_candidates.keywords':-</b> </p>\n" . mysql_error();
			} else {
				echo "<p>Field 'RAM_candidates.keywords' added OK.</p>\n";
			}
		} // eof check for record found
	}
	// now check the RAM_RateBands table for the new display_order field
	$query = "SHOW COLUMNS FROM `".$dbprefix."RAM_RateBands` LIKE 'display_order' ";
	$answer = mysql_query($query);
	// check for any error
	if (mysql_error()) { // check for problem
		echo "<p><b>Error reading column data:-</b> </p>\n" . mysql_error();
	} else {
		$num_rows=mysql_num_rows($answer);
		// Field found?
		if ($num_rows > 0 ) {
//			echo "<p>Field 'display_order' already exists.</p>\n";
		} else { // No, add it
//			echo "<p>Field 'display_order' not found!</p>\n";
			$query2 = "ALTER TABLE `".$dbprefix."RAM_RateBands` ADD COLUMN `display_order` int(4) NOT NULL default '0' ";
			$answer2 = mysql_query($query2);
			// check for any error
			if (mysql_error()) {
				echo "<p><b>Error adding field 'RAM_RateBands.display_order':-</b> </p>\n" . mysql_error();
			} else {
				echo "<p>Field 'RAM_RateBands.display_order' added OK.</p>\n";
			}
		} // eof check for record found
	}
	// now check the RAM_jobs table for the new submittedby field
	$query = "SHOW COLUMNS FROM `".$dbprefix."RAM_jobs` LIKE 'submittedby' ";
	$answer = mysql_query($query);
	// check for any error
	if (mysql_error()) { // check for problem
		echo "<p><b>Error reading column data:-</b> </p>\n" . mysql_error();
	} else {
		$num_rows=mysql_num_rows($answer);
		// Field found?
		if ($num_rows > 0 ) {
//			echo "<p>Field 'submittedby' already exists.</p>\n";
		} else { // No, add it
//			echo "<p>Field 'submittedby' not found!</p>\n";
			$query2 = "ALTER TABLE `".$dbprefix."RAM_jobs` ADD COLUMN `submittedby` int(11) NOT NULL default '0' ";
			$answer2 = mysql_query($query2);
			// check for any error
			if (mysql_error()) {
				echo "<p><b>Error adding field 'RAM_jobs.submittedby':-</b> </p>\n" . mysql_error();
			} else {
				echo "<p>Field 'RAM_jobs.submittedby' added OK.</p>\n";
			}
		} // eof check for record found
	}
	// now check the RAM_interviews table for the new applicationid field
	$query = "SHOW COLUMNS FROM `".$dbprefix."RAM_interviews` LIKE 'applicationid' ";
	$answer = mysql_query($query);
	// check for any error
	if (mysql_error()) { // check for problem
		echo "<p><b>Error reading column data:-</b> </p>\n" . mysql_error();
	} else {
		$num_rows=mysql_num_rows($answer);
		// Field found?
		if ($num_rows > 0 ) {
//			echo "<p>Field 'applicationid' already exists.</p>\n";
		} else { // No, add it
//			echo "<p>Field 'applicationid' not found!</p>\n";
			$query2 = "ALTER TABLE `".$dbprefix."RAM_interviews` ADD COLUMN `applicationid` int(11) NOT NULL default '0' COMMENT 'Ties an interview to an application' ";
			$answer2 = mysql_query($query2);
			// check for any error
			if (mysql_error()) {
				echo "<p><b>Error adding field 'RAM_interviews.applicationid':-</b> </p>\n" . mysql_error();
			} else {
				echo "<p>Field 'RAM_interviews.applicationid' added OK.</p>\n";
			}
		} // eof check for record found
	}

	// now see if any RAM_FrontPage records exists
	$query2 = "SELECT * FROM `".$dbprefix."RAM_FrontPage` ";
	$answer2 = mysql_query($query2);
	$num_rows2=mysql_num_rows($answer2);
	// any records found?
	if ($num_rows2 == 0 ) {
		// no, insert data in new RAM_FrontPage table
		echo "<p>Inserting data into RAM_FrontPage table.</p>\n";
		$query4 = "INSERT INTO `".$dbprefix."RAM_FrontPage` (`id`, `front_heading`, `front_paragraph`, `front_paragraph2`, `front_paragraph3`) VALUES (1, 'My Recruitment Agency', 'You can search for jobs using the form below. Register with us to see jobs relevant to you. Login to see any applications you have made, or any interviews that have been arranged for you.', '<b>Search Tip:</b> To find jobs for PHP programming, just enter \'php\' in the box above. (Without the quotes.)', 'Your search results are above. Click on the links to see detailed information about any job you are interested in.' ) ON DUPLICATE KEY UPDATE `front_heading` = VALUES(`front_heading`),  `front_paragraph` = VALUES(`front_paragraph`),  `front_paragraph2` = VALUES(`front_paragraph2`),  `front_paragraph3` = VALUES(`front_paragraph3`);";
		mysql_query($query4);
		// check for any error
		if (mysql_error()) {
			echo "<p><b>Error inserting the RAM_FrontPage data:-</b> $query4</p>\n" . mysql_error();
		} else {
			echo "<p>RAM_FrontPage data inserted OK.</p>\n";
		} // eof check for any error
	} // eof check for any records found

	// first check the RAM_applications table for the new cvurl field
	$query = "SHOW COLUMNS FROM `".$dbprefix."RAM_applications` LIKE 'cvurl' ";
	$answer = mysql_query($query);
	// check for any error
	if (mysql_error()) { // check for problem
		echo "<p><b>Error reading column data:-</b> </p>\n" . mysql_error();
	} else {
		$num_rows=mysql_num_rows($answer);
		// Field found?
		if ($num_rows > 0 ) {
//			echo "<p>Field 'cvurl' already exists.</p>\n";
		} else { // No, add it
//			echo "<p>Field 'cvurl' not found!</p>\n";
			$query2 = "ALTER TABLE `".$dbprefix."RAM_applications` ADD COLUMN `cvurl` varchar(128) default NULL ";
			$answer2 = mysql_query($query2);
			// check for any error
			if (mysql_error()) {
				echo "<p><b>Error adding field 'RAM_applications.cvurl':-</b> </p>\n" . mysql_error();
			} else {
				echo "<p>Field 'RAM_applications.cvurl' added OK.</p>\n";
			}
		} // eof check for record found
	}
	// now check the RAM_candidates table for the new fullname field
	$query = "SHOW COLUMNS FROM `".$dbprefix."RAM_candidates` LIKE 'fullname' ";
	$answer = mysql_query($query);
	// check for any error
	if (mysql_error()) { // check for problem
		echo "<p><b>Error reading column data:-</b> </p>\n" . mysql_error();
	} else {
		$num_rows=mysql_num_rows($answer);
		// Field found?
		if ($num_rows > 0 ) {
//			echo "<p>Field 'fullname' already exists.</p>\n";
		} else { // No, add it
//			echo "<p>Field 'fullname' not found!</p>\n";
			$query2 = "ALTER TABLE `".$dbprefix."RAM_candidates` ADD COLUMN `fullname` varchar(128) NOT NULL default '' COMMENT 'To make translating candidate details with Joomfish easier' ";
			$answer2 = mysql_query($query2);
			// check for any error
			if (mysql_error()) {
				echo "<p><b>Error adding field 'RAM_candidates.fullname':-</b> </p>\n" . mysql_error();
			} else {
				echo "<p>Field 'RAM_candidates.fullname' added OK.</p>\n";
			}
		} // eof check for record found
	}

} // eof function
?>
